<?php
/**
 * @package model.dao
 * @author Mufeed
 * @copyright Copyright &copy; 2011, synergyquad
 * @since 1.0 - Oct-18-2012
 * @version 1.1
 */

/*require_once 'vo/UserVO.php';
require_once 'vo/AdminVO.php';
require_once 'vo/ForumVO.php';
require_once 'vo/CompanyVO.php';
require_once 'vo/AttachmentVO.php';
require_once 'vo/NotificationVO.php';*/


class DashboardDAOImpl
{
	
	/**
	 * @access public
	 * @staticvar Instance of MemberDAOImpl
	 */
	public static $_instance	= null;
	
	
	/**
	 * <p>Get an instance of MakeDAOImpl</p>
	 * 
	 * @access public
	 * @return MakeDAOImpl Instance of MakeDAOImpl
	 */
	public static function getInstance() 
	{
		if(self::$_instance == null) 
		{
			self::$_instance = new DashboardDAOImpl();
		}
		
		return self::$_instance;
	}
        
       public function getVehicleCount()
    {
        $result[] = '';
      
        $ns_newCars_result = mysql_query("SELECT COUNT(*) as total FROM `vehicle` WHERE is_read=0 AND status='New Submission'");
        $ns_newCars_row = mysql_fetch_array($ns_newCars_result);
        $ba_newCars_result = mysql_query("SELECT COUNT(*) as total FROM `vehicle` WHERE is_read=0 AND status='Bid Accepted'");
        $ba_newCars_row = mysql_fetch_array($ba_newCars_result);
        $np_newCars_result = mysql_query("SELECT COUNT(*) as total FROM `vehicle` WHERE is_read=0 AND status='Now In Possession'");
        $np_newCars_row = mysql_fetch_array($np_newCars_result);
        
        $all_Cars_result = mysql_query("SELECT COUNT(*) as total FROM `vehicle`");
        $all_Cars_row = mysql_fetch_array($all_Cars_result);
        
        $ns_countmsg_result = mysql_query("SELECT count( m.message_id ) as total FROM vehicle v, message m WHERE v.status='New Submission' AND v.vehicle_id = m.vehicle_id AND m.is_read =0");
        $ns_countmsg_row = mysql_fetch_array($ns_countmsg_result);
        $ba_countmsg_result = mysql_query("SELECT  count( m.message_id ) as total FROM vehicle v, message m WHERE v.status='Bid Accepted' AND v.vehicle_id = m.vehicle_id AND m.is_read =0");
        $ba_countmsg_row = mysql_fetch_array($ba_countmsg_result);
        $np_countmsg_result = mysql_query("SELECT  count( m.message_id ) as total FROM vehicle v, message m WHERE v.status='Now In Possession' AND v.vehicle_id = m.vehicle_id AND m.is_read =0");
        $np_countmsg_row = mysql_fetch_array($np_countmsg_result);
        
        $all_countmsg_result = mysql_query("SELECT  count( m.message_id ) as total FROM vehicle v, message m WHERE v.vehicle_id = m.vehicle_id");
        $all_countmsg_row = mysql_fetch_array($all_countmsg_result);
        
		//$cReminder = mysql_query("SELECT count( * ) as total_record  FROM `watchlist`");
        $cReminder = mysql_query("SELECT count( * ) as total_record, (SELECT count(*) FROM `watchlist` WHERE to_date<now()) as due, (SELECT count(*) FROM `watchlist` WHERE to_date BETWEEN now() and ADDDATE(now(),10)) as duesoon  FROM `watchlist`");
        $cReminder_row= mysql_fetch_array($cReminder);
        
        $locked_row = mysql_fetch_array(mysql_query("SELECT (SELECT count(*) as locked FROM vehicle WHERE is_locked=1)as locked, (SELECT count(*) as locked FROM vehicle WHERE status='Deleted') as deleted FROM vehicle"));
		
        $result["new_count"] = $ns_newCars_row['total'];
        $result["ba_count"] = $ba_newCars_row['total'];
        $result["np_count"] = $np_newCars_row['total'];
        
        $result["new_replies"] = $ns_countmsg_row['total'];
        $result["ba_replies"] = $ba_countmsg_row['total'];
        $result["np_replies"] = $np_countmsg_row['total'];
        
        $result["all_vehicles"] = $all_Cars_row['total'];
        $result["all_replies"] = $all_countmsg_row['total'];
        
        
        $result["total_reminder"] = $cReminder_row['total_record'];
        $result["reminder_due"]=$cReminder_row['due'];
        $result["reminder_duesoon"]=$cReminder_row['duesoon'];
        
        $result["locked"]=$locked_row["locked"];
        $result["deleted"]=$locked_row["deleted"];
        
        
        return $result;
    }
	
    public function getReminders()
    {
      return  $result= mysql_query("SELECT watchlist.watch_id,watchlist.vehicle_id,user.first_name,user.last_name,DATE(watchlist.set_date_time) as set_date,watchlist.to_date,watchlist.remind_note,(CASE WHEN watchlist.to_date<now() THEN 1 ELSE 0 END)AS due, (CASE WHEN watchlist.to_date BETWEEN now() and ADDDATE(now(),10) THEN 1 ELSE 0 END)AS duesoon, DATEDIFF(DATE(watchlist.to_date), DATE(NOW())) AS rem_days FROM watchlist LEFT JOIN  vehicle v  ON v.vehicle_id=watchlist.vehicle_id LEFT JOIN user_master user ON watchlist.set_by=user.user_id ORDER BY watchlist.to_date ASC LIMIT 0,3");
          
    }
     public function getRecentVehicles()
    {
        return  $result=mysql_query("SELECT v.vehicle_id, v.year, v.status, v.first_name, v.email, v.bid_price, make.make, model.model_name as model FROM vehicle v LEFT JOIN vehicle_make make ON v.make=make.make_id LEFT JOIN vehicle_model model ON v.model=model.model_id ORDER BY v.update_date LIMIT 0,3");
    }
    
    public function getStatusChart()
    {   $status= array();
        $result=mysql_query("SELECT COUNT(*) as count,status FROM vehicle GROUP BY status");
        while($row = mysql_fetch_object($result))
        {
            array_push($status, "'".$row->status."', ".$row->count);
        }
        return $status;
    }
    
    public function getSubmissionChart()
    {
        
        $schart_data = null; 
        $schart_data["web_buyer"]="";$schart_data["web_seller"]="";$schart_data["mobile_buyer"]="";$schart_data["mobile_seller"]="";
        $result = mysql_query("SELECT SUM(s.via_web_seller) AS web_seller,
SUM(s.via_web_buyer) AS web_buyer, SUM(s.via_mobile_seller) AS mobile_seller, SUM(s.via_mobile_buyer) AS mobile_buyer, WEEK(v.date)
FROM submission_tracker AS s LEFT JOIN vehicle v ON v.vehicle_id=s.vehicle_id WHERE
v.date>=STR_TO_DATE(CONCAT('01,',MONTH(SYSDATE()),',',YEAR(SYSDATE())
),'%d,%m,%Y') and v.date<=sysdate() GROUP BY WEEK(v.date)");
        while($row = mysql_fetch_object($result))
        {
            $schart_data["web_buyer"].=$row->web_buyer.",";
            $schart_data["web_seller"].=$row->web_seller.",";
            $schart_data["mobile_buyer"].=$row->mobile_buyer.",";
            $schart_data["mobile_seller"].=$row->mobile_seller.",";
            
        }
        
        return $schart_data;
    }
    
   
    
    public function getQuarterChart()
    {
        $qchart_data = null;
        $i=0;
       $qchart_data["quarter_avg"]=""; $qchart_data["web_buyer"]="";$qchart_data["web_seller"]="";$qchart_data["mobile_buyer"]="";$qchart_data["mobile_seller"]=""; $qchart_data["web_buyer_tot"]=0;$qchart_data["web_seller_tot"]=0;$qchart_data["mobile_buyer_tot"]=0;$qchart_data["mobile_seller_tot"]=0;
        $result = mysql_query("SELECT SUM(s.via_web_seller) AS web_seller,
SUM(s.via_web_buyer) AS web_buyer, SUM(s.via_mobile_seller) AS mobile_seller, SUM(s.via_mobile_buyer) AS mobile_buyer, QUARTER(v.date)
FROM submission_tracker AS s LEFT JOIN vehicle v ON v.vehicle_id=s.vehicle_id WHERE YEAR(v.date)=YEAR(NOW()) GROUP BY QUARTER(v.date)");
        while($row = mysql_fetch_object($result))
        {
            $qchart_data["web_buyer"].=$row->web_buyer.",";
            $qchart_data["web_seller"].=$row->web_seller.",";
            $qchart_data["mobile_buyer"].=$row->mobile_buyer.",";
            $qchart_data["mobile_seller"].=$row->mobile_seller.",";
            
            $qchart_data["quarter_avg"].=strval(($row->web_buyer+$row->web_seller+$row->mobile_buyer+$row->mobile_seller)/4).", ";
            $qchart_data["web_buyer_tot"] += $row->web_buyer;
            $qchart_data["web_seller_tot"] += $row->web_seller;
            $qchart_data["mobile_buyer_tot"] += $row->mobile_buyer;
            $qchart_data["mobile_seller_tot"] += $row->mobile_seller;
            
        }
       
       
        return $qchart_data;
        
    }
    
    public function getRevenueChart()
    {
        $rchart_data="";
        $result = mysql_query("SELECT SUM(bid_price) as revenue, MONTH(date) FROM vehicle WHERE YEAR(NOW())=YEAR(date) GROUP BY MONTH(date)");
        while($row = mysql_fetch_object($result))
             $rchart_data.=$row->revenue.",";
        return $rchart_data;
    }
} 
?>